#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date:
# Update Date:
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'

"""可以从MS-WORD文档生成DDL SQL.


MS-WORD文档生成DDL SQL.


  Class: about class.
  Function(): about function.
"""

__author__ = 'gtlions'
import re
import time
import datetime
import sys
import os

import win32com.client

import kexshop.gtcom.commod


def removechars(cellvalue):
    text = re.sub(r"[\r\n\t\x07\x0b]", "", cellvalue)
    return text


if len(sys.argv) != 2:

    print 'You must provide the MS-WORD file.'
    print 'gp_gen_ddl_from_word.exe <wordfile.doc or wordfile.docx>'
    # exit()
else:
    wordfile = sys.argv[1]
    if os.path.isfile(wordfile):
        wordfile = os.path.abspath(wordfile)
    else:
        print wordfile
        print 'The file ' + wordfile + 'is not exist! The file must absolute path!'
        sys.exit()

app = win32com.client.Dispatch('Word.Application')
# doc = app.Documents.Open(FileName=u"C:/app/PyGTWin/11.doc")
doc = app.Documents.Open(FileName=wordfile)
ddl_sql_file_name = 'GP_GEN_DDL_FROM_WORD.sql'
ddl_sql_file = open(ddl_sql_file_name, 'w')
ddl_sql_file.truncate()
ddl_sql_file.write("-- Generate GreenPlum DDL SQL From MS-WORD --\n")
ddl_sql_file.close()

for tabcounts in range(doc.Tables.count):
    wordtable = doc.Tables(tabcounts + 1)

    database = removechars(wordtable.Cell(1, 2).Range.Text)
    owner = removechars(wordtable.Cell(1, 4).Range.Text)
    schema = removechars(wordtable.Cell(1, 6).Range.Text)
    table = removechars(wordtable.Cell(2, 2).Range.Text)
    print (str(tabcounts + 1).rjust(10, '-') + ' Start table [' + table + '].')
    table_commnet = removechars(wordtable.Cell(2, 4).Range.Text)
    status = removechars(wordtable.Cell(2, 6).Range.Text)

    distributed_type = removechars(wordtable.Cell(3, 2).Range.Text)
    distributed_key = removechars(wordtable.Cell(3, 4).Range.Text)
    appendonly = removechars(wordtable.Cell(3, 6).Range.Text)

    orientation = removechars(wordtable.Cell(4, 2).Range.Text)
    compresstype = removechars(wordtable.Cell(4, 4).Range.Text)
    compresslevel = removechars(wordtable.Cell(4, 6).Range.Text)

    one_part = removechars(wordtable.Cell(5, 2).Range.Text)
    one_part_type = removechars(wordtable.Cell(5, 4).Range.Text)
    one_part_key = removechars(wordtable.Cell(5, 6).Range.Text)
    one_part_format = removechars(wordtable.Cell(6, 2).Range.Text)
    one_part_start = removechars(wordtable.Cell(6, 4).Range.Text)
    one_part_end = removechars(wordtable.Cell(6, 6).Range.Text)
    two_part = removechars(wordtable.Cell(7, 2).Range.Text)
    two_part_type = removechars(wordtable.Cell(7, 4).Range.Text)
    two_part_key = removechars(wordtable.Cell(7, 6).Range.Text)
    two_part_format = removechars(wordtable.Cell(8, 2).Range.Text)
    two_part_start = removechars(wordtable.Cell(8, 4).Range.Text)
    two_part_end = removechars(wordtable.Cell(8, 6).Range.Text)
    # print database
    # print owner
    # print schema
    # print table
    # print table_commnet
    # print status
    # print distributed_type
    # print distributed_key
    # print appendonly
    # print orientation
    # print compresstype
    # print compresslevel
    # print one_part
    # print one_part_type
    # print one_part_key
    # print one_part_format
    # print one_part_start
    # print one_part_end
    # print two_part
    # print two_part_type
    # print two_part_key
    # print two_part_format
    # print two_part_start
    # print two_part_end
    start_commnet = '\n----------' + str(tabcounts + 1) + ' ' + table + '----------\n'
    ddl_commnet = '\n-- Create table ' + schema + '.' + table + ' On Database[' + database + '] Owner [' + owner + '].\n--Drop table if exists ' + schema + '.' + table + ';\n'
    table_header_sql = '\nCreate table ' + schema + '.' + table + '\n'
    column_list_sql = ''
    index_sql = ''
    column_commnet_sql = ''
    with_sql = ''
    distributed_sql = ''
    one_part_sql = ''
    one_part_lsit_sql = ''
    table_owner_sql = ''
    table_commnet_sql = ''
    one_part_key_type = ''
    for r in range(10, wordtable.Rows.Count):
        # column_list_sql
        column_name = removechars(wordtable.Cell(r + 1, 1).Range.Text)
        column_type = removechars(wordtable.Cell(r + 1, 2).Range.Text)
        if one_part_key == column_name:
            one_part_key_type = column_type
            # print one_part_key, one_part_key_type,column_type
        column_list_sql += column_name + ' ' + column_type
        if removechars(wordtable.Cell(r + 1, 3).Range.Text).upper() == 'N':
            column_list_sql += ' not null'
        if removechars(wordtable.Cell(r + 1, 4).Range.Text).upper() == 'Y':
            column_list_sql += ' primary key'
        if r == wordtable.Rows.Count - 1:
            pass
        else:
            column_list_sql += ',\n'

        # index_sql
        if removechars(wordtable.Cell(r + 1, 5).Range.Text).upper() == 'Y':
            index_sql += 'create index idx_' + table + '_' + column_name + ' on ' + schema + '.' + table + '(' + column_name + ');\n'
        # column_commnet_sql
        column_commnet_text = removechars(wordtable.Cell(r + 1, 6).Range.Text)
        if removechars(wordtable.Cell(r + 1, 6).Range.Text):
            column_commnet_sql += "comment on column " + schema + '.' + table + '.' + column_name + " is '" + column_commnet_text + "';\n"
    column_list_sql = '(\n' + column_list_sql + '\n)\n'
    # print column_list_sql
    if appendonly.upper() == 'Y':
        with_sql = 'with (appendonly=true,orientation=' + orientation
        if compresstype.upper() == 'ZLIB':
            with_sql += ',compresstype=zlib,compresslevel=' + compresslevel
        elif compresstype.upper() == 'QUICKLZ':
            with_sql += ',compresstype=quicklz'
        else:
            with_sql += ''
        with_sql += ')'

    if distributed_type.upper() == 'HASH':
        distributed_sql = '\ndistributed by (' + distributed_key + ')';
    else:
        distributed_sql = '\ndistributed randomly'

    # partitions by one level
    if one_part.upper() == 'Y':
        distributed_sql += '\n';
        one_part_sql = 'partition by ' + one_part_type + '(' + one_part_key + ')\n'

        if one_part_format.upper() == 'YYYYMMDD':
            y, m, d = time.strptime(one_part_start, '%Y%m%d')[0:3]
            o_d1_d = datetime.date(y, m, d)
            o_d1 = datetime.date(y, m, d).strftime('%Y%m%d')

            y, m, d = time.strptime(one_part_end, '%Y%m%d')[0:3]
            o_d2_d = datetime.date(y, m, d)
            o_d2 = datetime.date(y, m, d).strftime('%Y%m%d')
            for i in range((o_d2_d - o_d1_d).days):
                start = (o_d1_d + datetime.timedelta(days=i)).strftime('%Y%m%d')
                end = (o_d1_d + datetime.timedelta(days=i + 1)).strftime('%Y%m%d')
                if one_part_key_type in ['timestamp']:
                    one_part_lsit_sql += "partition " + one_part_key + start + " start('" + start + "'::date) end ('" + end + "'::date) " + with_sql
                else:
                    one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                if i < (o_d2_d - o_d1_d).days - 1:
                    one_part_lsit_sql += ',\n'
            one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
        elif one_part_format.upper() == 'YYYYMM':
            one_part_start = str(int(one_part_start))
            one_part_end = str(int(one_part_end))
            mondict = kexshop.gtcom.commod.GetMonDict(one_part_start, one_part_end)
            for start, end in [(k, mondict[k]) for k in sorted(mondict.keys())]:
                one_part_lsit_sql += 'partition ' + one_part_key + start + ' start(' + start + ') end (' + end + ') ' + with_sql
                if start != one_part_end:
                    one_part_lsit_sql += ',\n'
            one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
        elif one_part_format.upper() == 'MM':
            for i in range(12):
                start = i + 1
                end = start + 1
                one_part_lsit_sql += 'partition ' + one_part_key + str(start) + ' start(' + str(
                    start) + ') end (' + str(end) + ') ' + with_sql
                if start < 12:
                    one_part_lsit_sql += ',\n'
            one_part_lsit_sql = '(\n' + one_part_lsit_sql + '\n);\n'
    else:
        distributed_sql += ';\n'

    ddl_sql_file = open(ddl_sql_file_name, 'a')
    ddl_sql_file.write(
        start_commnet.encode('utf8') + ddl_commnet.encode('utf8') + table_header_sql.encode(
            'utf8') + column_list_sql.encode('utf8') + with_sql.encode(
            'utf8') + distributed_sql.encode('utf8') + one_part_sql.encode('utf8') + one_part_lsit_sql.encode('utf8'))
    # print ddl_commnet + table_header+ column + with_sql + distributed_sql + one_part_sql + one_part_lsit_sql

    index_commnet = '\n-- Create Index\n'
    ddl_sql_file.write(index_commnet.encode('utf8') + index_sql.encode('utf8'))
    # print index_commnet + index

    table_commnet_comment = '\n-- Table comment\n'
    if table_commnet:
        table_commnet_sql = "comment on table " + schema + '.' + table + " is '" + table_commnet + "';"
    ddl_sql_file.write(table_commnet_comment + table_commnet_sql.encode('utf8'))
    # print  table_commnet_comment + table_commnet_sql

    column_commnet_commnet = '\n-- Column comment\n'
    ddl_sql_file.write(column_commnet_commnet + column_commnet_sql.encode('utf8'))
    # print  column_commnet_commnet + column_commnet

    table_owner_commnet = '\n-- Table owner\n'
    table_owner_sql = 'alter table ' + schema + '.' + table + ' owner to ' + owner + ';\n'
    ddl_sql_file.write(table_owner_commnet.encode('utf8') + table_owner_sql.encode('utf8'))
    # print table_owner_commnet + table_owner_sql
    ddl_sql_file.close()
